package test.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import util.StrKit;
import util.Test;
import cn.tsingyu.generator.db.Column;
import cn.tsingyu.generator.db.DbDataTypesUtils;
import freemarker.template.Configuration;
import freemarker.template.Template;


public class JdbcSqlServer {
	
	public static final String url = "jdbc:sqlserver://localhost:1433; DatabaseName=cmes";
	public static final String name = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	public static final String user = "sa";
	public static final String password = "000000";

	public static Connection conn = null;
	public PreparedStatement pst = null;

	public static void main(String[] args) {
		try {
			Class.forName(name);//指定连接类型  
			conn = DriverManager.getConnection(url, user, password);//获取连接
//			List tables = getTables(conn,null,null,"");
//			for (int i = 0; i < tables.size(); i++) {
				String tableName = "ROUTESTEP";
				String className = StrKit.firstCharToUpperCase(tableName);
				Configuration cfg = new  Configuration(Configuration.VERSION_2_3_23);  
				List<Column> columns = getTableColumns(conn, getCatalog(), getSchema(), tableName, getColumnPattern());
				cfg.setClassForTemplateLoading(Test.class ,  "/template" ); //指定模板所在的classpath目录   
	            Template t = cfg.getTemplate("entity1.ftl" ); //指定模板   
	            //FileOutputStream fos = new  FileOutputStream( new  File( "d:/Student.java")); //java文件的生成目录   
	            FileOutputStream fos = new  FileOutputStream( new  File( "src/template/"+className+".java"));
	              
	            //模拟数据源   
	            Map data = new  HashMap();  
	            data.put("package" ,  "template" ); //包名   
	            data.put("className" ,  className );  
	              
	            data.put("properties", columns);
	            t.process(data, new  OutputStreamWriter(fos, "utf-8" )); //   
	            fos.flush();  
	            fos.close();  
//			}
			conn.close();
		}  catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	private static String getCatalog(){
		return null;
	}
	private static String getSchema(){
		return null;
	}
	private static String getColumnPattern(){
		return "";
	}
	public static Map getSchemas(Connection c) throws SQLException {
		DatabaseMetaData dmd = c.getMetaData();
		ResultSet rs = null;
		try {
			rs = dmd.getSchemas();
			Map map = new HashMap();
			List l;
			while (rs.next()) {
				String schema = rs.getString(1);
				String catalog = null;
				if (rs.getMetaData().getColumnCount() > 1) {
					catalog = rs.getString(2);
					System.out.println("catalog:"+catalog);
				};
				l = (List)map.get(catalog);
				if (l == null) {
					l = new LinkedList();
					map.put(catalog, l);
				}
				l.add(schema);
			}
			return map;
		}
		finally {
			if (rs != null) rs.close();
		}
	}
	public static List getTables(Connection c, String catalog, String schema, String tablePattern) throws SQLException {
		DatabaseMetaData dmd = c.getMetaData();
		ResultSet rs = null;
		try {
			rs = dmd.getTables(catalog, schema, tablePattern, new String[] {"TABLE", "VIEW", "SYNONYM", "ALIAS"} );
			List l= new LinkedList();
			while (rs.next()) {
				l.add(rs.getString(3));
			}
			return l;
		}
		finally {
			if (rs != null) rs.close();
		}
	}
	public static List<Column> getTableColumns(Connection c, String catalog, 
			String schema, String table, String columnPattern) throws SQLException {
		DatabaseMetaData dmd = c.getMetaData();
		ResultSet rs = null;
		try {
			rs = dmd.getColumns(catalog, schema, table, columnPattern);
			List<Column> columns = new LinkedList<Column>();
			while (rs.next()) {
				Column column = new Column();
				column.setColumnName(rs.getString(4));
				column.setUpperColumnName(StrKit.firstCharToUpperCase(rs.getString(4)));
				column.setSqlType(rs.getShort(5));
				column.setSize(rs.getInt(7));
				column.setDecimalDigits(rs.getInt(9));
				column.setJavaTypeName(DbDataTypesUtils.getPreferredJavaType(rs.getShort(5), rs.getInt(7), rs.getInt(9)));
				columns.add(column);
			}
			return columns;
		}
		finally {
			if (rs != null) rs.close();
		}
		
	}

}
